package com.example.hrms.mapper;



import com.example.hrms.entity.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.util.List;


@Mapper
@Repository
public interface UserMapper {

    @Select("select * from user  where Username=#{0} and Password= #{1}")
    User checkUserLogin(@Param("0")String username, @Param("1")String password);

    @Select("select * from staff_personal_info")
    List<StaffPersonalInfo1> getAllStaffPersonalInfo();

    @Update("UPDATE staff_personal_info\n" +
            "SET Name=#{1},Sex=#{2},Phone=#{3},Address=#{4},Birthday=#{5}\n" +
            "WHERE PIid=#{0}")
    int  updatePersonalInfo(@Param("0")int piid,@Param("1")String name,@Param("2")int sex,@Param("3")String phone,@Param("4")String address,@Param("5")String birthday);

    @Delete("delete from staff_personal_info where PIid = #{0}")
    int deletePersonalInfo(@Param("0") int piid);

    @Select("SELECT `Name` ,m.*\n" +
            "FROM staff_personal_info i JOIN staff_personnel_matter m on i.Uid=m.Uid")
    List<PersonnelMatterInfo> getAllPersonenlMatterlInfo();

    @Update("UPDATE staff_personnel_matter\n" +
            "SET Join_time=#{1},Department=#{2},Position=#{3},Salary=#{4},Is_regular=#{5}\n" +
            "WHERE PMid=#{0}")
    int updatePersonnelMatter(@Param("0")int pmid,@Param("1")String joinTime,@Param("2")String departmen,@Param("3")String position,@Param("4")int salary,@Param("5")int isRegular);
    @Insert("insert into user(Username,Password,Create_time,Role_id) values(#{Username},#{Password},#{Create_time}," +
            "#{Role_id})")
    int addUser(User user);
    @Select("select * from user  where Username=#{0}")
    User checkUsername(@Param("0") String uesrname);
    @Insert("insert into staff_personal_info(Uid,Name,Sex,Phone,Address,Birthday) values(#{Uid},#{Name},#{Sex}," +
            "#{Phone},#{Address},#{Birthday})")
    int addPersonalInfo(StaffPersonalInfo1 NewPersonalInfo);
    @Insert("insert into staff_personnel_matter(Uid,Join_time,Department,Position,Salary,Is_regular) values(#{Uid},#{Join_time},#{Department}," +
            "#{Position},#{Salary},#{Is_regular})")
    int addPersonnelMatter(StaffPersonnelMatter NewPersonnelInfo);

    @Select("SELECT u.*,`Name`\n" +
            "FROM `user` u JOIN staff_personal_info p on u.Uid=p.Uid")
    List<StaffAccountInfo> getAllStaffAccountInfo();
    @Update("UPDATE user\n" +
            "SET Username=#{1},Password=#{2},Modify_time=#{3}\n" +
            "WHERE Uid=#{0}")
    int updateUser(@Param("0")int uid,@Param("1")String username,@Param("2") String password, @Param("3")Timestamp modify);
    @Delete("delete from user where Uid = #{0}")
    int deleteUser(@Param("0") int uid);
    @Select("SELECT r.*,`Name`\n" +
            "FROM staff_reward_punishment r JOIN staff_personal_info p on r.Uid=p.Uid")
    List<RPInfo> getAllRPInfo();
    @Insert("insert into staff_reward_punishment(Uid,Type,Detail,Time) values(#{0},#{1},#{2},#{3})")
    int addStaffRP(@Param("0") int uid,@Param("1")String type,@Param("2")String detail,@Param("3")String time);
    @Select("SELECT *\n" +
            "          FROM `user` where Role_id=1 and Uid=#{0}")
    User checkStaffID(@Param("0") int uid);

    @Delete("delete from staff_reward_punishment where RPid = #{0}")
    int deleteRPInfo(@Param("0") int rpid);
    @Update("UPDATE staff_reward_punishment\n" +
            "SET Type=#{1},Detail=#{2},Time=#{3}\n" +
            "WHERE RPid=#{0}")
    int updateStaffRP(@Param("0") int rpid,@Param("1")String type,@Param("2")String detail,@Param("3")String RPTime);

    @Select("SELECT r.*,d.Uid,d.Note,d.RUid\n" +
            "FROM road_info r LEFT JOIN driver_assign d\n" +
            "ON r.Rid=d.Rid")
    List<RoadInfo> getAllRoadInfo();

    @Select("SELECT pm.Uid,`Name`\n" +
            "FROM staff_personnel_matter pm JOIN staff_personal_info pi\n" +
            "ON pm.Uid=pi.Uid\n" +
            "WHERE Position=\"司机\"")
    List<DriverInfo> getAllDriver();

    @Select("SELECT *\n" +
            "FROM road_info")
    List<Road_Info>getAllRoad();

    @Insert("insert into driver_assign(Uid,Rid,Note) values(#{0},#{1},#{2})")
    int addDriver(@Param("1")int rid,@Param("0") int uid,@Param("2")String note);
    @Select("SELECT *\n" +
            "          FROM driver_assign where Uid=#{1}")
    Integer findDAByUid(@Param("1")int uid);

    @Select("SELECT *\n" +
            "          FROM driver_assign where Rid=#{0} and Uid=#{1}")
    Integer  findDAByRidUid(@Param("0") int rid,@Param("1") int uid);

    @Select("SELECT *\n" +
            "FROM road_info where Road_number=#{0}")
    Integer  findRoadByRoadNumber(@Param("0") String roadNumber);

    @Insert("insert into road_info(Road_number,Line,Create_time) values(#{0},#{1},#{2})")
    int addRoad(@Param("0") String roadNumber,@Param("1") String line,@Param("2") Timestamp createTime);

    @Delete("delete from driver_assign where Uid = #{0} and Rid=#{1}")
    int deleteAssign(@Param("0") int uid,@Param("1") int rid);

    @Delete("delete from road_info where Rid=#{1}")
    int deleteRoad(@Param("1")int rid);

    @Update("UPDATE road_info\n" +
            "SET Road_number=#{1},Line=#{2},Modify_time=#{3}\n" +
            "WHERE Rid=#{0}")
    int updateRoadInfo(@Param("0") int rid,@Param("1")String roadUnmber,@Param("2")String line,@Param("3")Timestamp t);


    @Update("UPDATE driver_assign\n" +
            "SET Uid=#{1},Note=#{2}\n" +
            "WHERE RUid=#{0}")
    int updateDriverAssign(@Param("0")int ruid,@Param("1")int uid,@Param("2")String note);

    @Select("SELECT s.*,u.Username\n" +
            "FROM staff_personal_info s JOIN `user` u\n" +
            "ON s.Uid=u.Uid\n" +
            "WHERE u.Username=#{0}")
    StaffPersonalInfo1 getStaffPersonalInfoByUsername(@Param("0") String username);

    @Update("UPDATE user\n" +
            "SET Password=#{1}\n" +
            "WHERE Username=#{0}")
    int updatePwdByUsername(@Param("0") String username,@Param("1")String  pwd);

    @Select("SELECT *\n" +
            "FROM staff_personal_info pi JOIN staff_personnel_matter pm\n" +
            "ON pi.Uid=pm.Uid\n" +
            "WHERE pi.Uid=#{0}")
    StaffInfo getStaffInfo(@Param("0") int uid);


    @Select("SELECT *\n" +
            "FROM staff_reward_punishment\n" +
            "WHERE Uid=#{0}")
    List<StaffRPInfo> getRPInfoByUid(@Param("0") int uid);


    @Insert("insert into staff_compassionate_leave(Uid,Type,Start_time,End_time,SignUp_time,Detail,Status) values(#{0},#{1},#{2},#{3},#{4},#{5},0)")
    int addStaffCL(@Param("0")int uid,@Param("1")String CLtype,@Param("2")String startTime,@Param("3")String endTime,@Param("4")Timestamp signUpTime,@Param("5")String CLDetail);


    @Select("SELECT *\n" +
            "FROM staff_compassionate_leave\n" +
            "WHERE Uid=#{0}")
    List<StaffCL> getCLListByUid(@Param("0")int uid);

    @Select("SELECT *\n" +
            "FROM staff_compassionate_leave\n" +
            "WHERE Uid=#{3} AND " +
            " ((Start_time >= #{0} AND Start_time <= #{1})\n" +
            "    OR (Start_time <= #{0} AND End_time >= #{1})\n" +
            "    OR (End_time >= #{0}AND End_time <= #{1}))")
    List<StaffCL> checkCLTime(@Param("0")String start,@Param("1")String end,@Param("3")int uid);

    @Delete("delete from staff_compassionate_leave where CLid=#{1}")
    int deleteStaffCL(@Param("1")int clid);

    @Select("SELECT cl.*,Name\n" +
            "FROM staff_compassionate_leave cl LEFT join staff_personal_info pi\n" +
            "on cl.Uid=pi.Uid")
    List<CLInfo> getAllCLInfo();

    @Update("UPDATE staff_compassionate_leave\n" +
            "SET Status=3\n" +
            "WHERE Start_time<curtime() and Status=0")
    int overTimeCheck();


    @Update("UPDATE staff_compassionate_leave\n" +
            "SET Status=1 ,Pass_time=curtime()\n" +
            "WHERE CLid=#{0}")
    int passCL(@Param("0") int clid);

    @Update("UPDATE staff_compassionate_leave\n" +
            "SET Status=2\n" +
            "WHERE CLid=#{0}")
    int refuseCL(@Param("0") int clid);

    @Select("SELECT RUid,d.Uid,d.Rid,Road_number,Line\n" +
            "FROM `driver_assign` d JOIN  road_info r\n" +
            "ON d.Rid=r.Rid\n" +
            "WHERE Uid=#{0}")
    DriverAssignInfo getDriverInfo(@Param("0") int uid);
}
